package models

import (
	//	"errors"
	//	"fmt"
	//	"reflect"
	//	"strings"
	//	"time"
	"gitee.com/lzqysoft/ilive/libs"

	"github.com/astaxie/beego"
	"github.com/astaxie/beego/orm"
)

//Desc 统计单位情况
//return 统计结果，异常
func StaticOrg() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT a.id,a.abbr as name," +
		" Count(b.org_id) AS clicks," +
		" sum(d.logincount) as logincount," +
		" Sum(b.looklong) AS looklong " +
		" FROM ((public.organization a LEFT JOIN public.watchhistory b" +
		" ON a.id= b.org_id) " +
		" left JOIN public.user c ON a.id = c.organization_id)" +
		" left JOIN public.userprof d on d.id = c.id " +
		" where a.status = 1 and a.isdelete=0  " +
		" GROUP BY a.id " +
		" ORDER BY a.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc  统计全部视频观看情况
//return []orm.Params, error
func StaticVideo() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM video b LEFT JOIN public.watchhistory a " +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据单位统计视频观看情况
//Param user *User
//return []orm.Params, error
func StaticVideoByOrgId(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM video b LEFT JOIN public.watchhistory a " +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.organization_id = ? " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据部门统计视频观看情况
//Param user *User
//return []orm.Params, error
func StaticVideoByDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	//	str := libs.SliceToStr(",", *GetDeptChildrenStr(user.Dept[0].Id))
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM video b LEFT JOIN public.watchhistory a " +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.dept_id =? " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()

	_, err := o.Raw(sql, user.Dept[0].Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc  统计全部直播观看情况
//return []orm.Params, error
func StaticLive() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM live b LEFT JOIN public.watchhistory a" +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据单位统计直播观看情况
//Param user *User
//return []orm.Params, error
func StaticLiveByOrgId(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM live b LEFT JOIN public.watchhistory a" +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.organization_id =? " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据部门统计直播观看情况
//Param user *User
//return []orm.Params, error
func StaticLiveByDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	//	str := libs.SliceToStr(",", *GetDeptChildrenStr(user.Dept[0].Id))
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM live b LEFT JOIN public.watchhistory a" +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.dept_id =? " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Dept[0].Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 统计全部人员的登录及观看情况
//Param
//return 统计结果，异常
func StaticUser() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT a.id,a.name as code,b.title as uname," +
		" d.name as oname,f.name as dname," +
		" b.logincount,Sum(c.looklong)as looklong ," +
		" count(c.user_id) as clicks" +
		" FROM ( public.user a INNER JOIN public.userprof b ON a.id=b.id )" +
		" LEFT JOIN  public.watchhistory c ON a.id=c.user_id" +
		" LEFT JOIN organization d on a.organization_id= d.id" +
		" LEFT JOIN user_depts e on a.id=e.user_id" +
		" LEFT JOIN dept f on e.dept_id=f.id" +
		" WHERE a.status = 1 and a.isdelete=0" +
		" GROUP BY a.id,a.name,b.logincount,d.name,f.name," +
		" b.title " +
		" ORDER BY a.Organization_id DESC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据单位统计人员的登录及观看情况
//Param user *User
//return 统计结果，异常
func StaticUserByOrgId(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT a.id,a.name as code,b.title as uname," +
		" d.name as oname,f.name as dname," +
		" b.logincount,Sum(c.looklong)as looklong ," +
		" count(c.user_id) as clicks" +
		" FROM ( public.user a INNER JOIN public.userprof b ON a.id=b.id )" +
		" LEFT JOIN  public.watchhistory c ON a.id=c.user_id" +
		" LEFT JOIN organization d on a.organization_id= d.id" +
		" LEFT JOIN user_depts e on a.id=e.user_id" +
		" LEFT JOIN dept f on e.dept_id=f.id" +
		" WHERE a.status = 1 and a.isdelete=0" +
		"  and a.Organization_id = ?" +
		" GROUP BY a.id,a.name,b.logincount,d.name,f.name," +
		" b.title " +
		" ORDER BY a.Organization_id DESC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据部门统计人员的登录及观看情况
//Param user *User
//return 统计结果，异常
func StaticUserByDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	str := libs.SliceToStr(",", *GetDeptChildrenStr(user.Dept[0].Id))
	sql := " SELECT a.id,a.name as code,b.title as uname," +
		" d.name as oname,f.name as dname," +
		" b.logincount,Sum(c.looklong)as looklong ," +
		" count(c.user_id) as clicks" +
		" FROM ( public.user a INNER JOIN public.userprof b ON a.id=b.id )" +
		" LEFT JOIN  public.watchhistory c ON a.id=c.user_id" +
		" LEFT JOIN organization d on a.organization_id= d.id" +
		" LEFT JOIN user_depts e on a.id=e.user_id" +
		" LEFT JOIN dept f on e.dept_id=f.id" +
		" WHERE a.status = 1 and a.isdelete=0" +
		" and e.dept_id in (" + str + ")" +
		" GROUP BY a.id,a.name,b.logincount,d.name,f.name," +
		" b.title " +
		" ORDER BY a.Organization_id DESC"
	o := orm.NewOrm()
	beego.Debug(sql)
	beego.Debug(str)
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 统计部门情况,包括下属部门
//Param user *User
//return 统计结果，异常
func StaticDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	str := GetDeptSql(user.Dept[0].Id)
	sql := " SELECT a.id,a.name as name," +
		" Count(b.dept_id) AS clicks," +
		" sum(d.logincount) as logincount," +
		" Sum(b.looklong) AS looklong " +
		" FROM ((public.dept a LEFT JOIN public.watchhistory b" +
		" ON a.id= b.dept_id) " +
		" left JOIN public.user_depts c ON a.id = c.dept_id)" +
		" left JOIN public.userprof d on d.id = c.user_id" +
		" where a.status = 1 and a.isdelete=0" +
		" and a.organization_id= ?" + str +
		" GROUP BY a.id " +
		" ORDER BY a.seq ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//StaticUsedDiskAll 查看all单位已用空间的百分比
//int64 已用空间百分比
func StaticUsedDiskAll() ([]orm.Params, error) {
	o := orm.NewOrm()
	var maps []orm.Params
	sql := `
SELECT ta.name "name",ta.userlimit/1024 olimit,sum(tb.size)/1024 as oused

FROM organization ta 
left JOIN video tb 
on 
ta.id=tb.organization_id
GROUP BY ta.name,ta.userlimit
`
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["oused"] == nil {
			v["oused"] = 0
		}
	}
	return maps, err
}
